package com.own.utils;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.record.formula.functions.T;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 * 继承spring jdbctemplate 增加常用方法
 * @author mjm
 * @history 2011-5-4
 */
@SuppressWarnings("unchecked")
public class JDBCTemplate extends JdbcTemplate {
	
	public JDBCTemplate() {
		super();
	}
	
	public JDBCTemplate(DataSource dataSource) {
		super(dataSource);
	}

	/**
	 * 查询Maplist，Map为<列名，结果>
	 * @author mjm
	 * @history 2011-5-4
	 * @param sql 查询语句
	 * @return
	 */
	public List queryForMapList(String sql) {
		return queryForMapList(sql, null);
	}

	/**
	 * 查询Maplist，Map为<列名，结果>
	 * @author mjm
	 * @history 2011-5-4
	 * @param sql 查询语句
	 * @param args 参数
	 * @return
	 */
	public List queryForMapList(String sql, Object[] args) {
		return query(sql, args, new RowMapper() {
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				Map m = new HashMap();
				ResultSetMetaData metaData = rs.getMetaData();
				int count = metaData.getColumnCount();
				for (int j = 0; j < count; j++) {
					String fieldName = metaData.getColumnName(i + 1);
					m.put(fieldName, rs.getObject(fieldName));
				}
				return m;
			}
		});
	}

	/**
	 * 查询一个String对象
	 * @author mjm
	 * @history 2011-5-4
	 * @param sql
	 * @return
	 */
	public String queryForString(String sql) {
		return queryForString(sql, null);
	}

	/**
	 * 查询一个String对象
	 * @author mjm
	 * @history 2011-5-4
	 * @param sql
	 * @param args
	 * @return
	 */
	public String queryForString(String sql, Object[] args) {
		return (String) queryForObject(sql, args, String.class);
	}
	
	public List<String> queryForStringList(String sql) {
		return queryForStringList(sql, null);
	}
	/**
	 * 查询stringList
	 * @author mjm
	 * @history 2011-9-27
	 * @param sql
	 * @param args
	 * @return
	 */
	public List<String> queryForStringList(String sql, Object[] args) {
		return queryForList(sql, args, String.class);
	}

	/**
	 * 查询beanlist
	 * @author mjm
	 * @history 2011-3-9
	 * @param sql sql查询语句
	 * @param bean bean对应class
	 * @return beanlist
	 */
	public List queryForBeanList(String sql, Class bean) {
		return this.queryForBeanList(sql, null, bean);
	}
	
	public List queryForBeanList(String sql, Object[] args, Class bean) {
		return query(sql, args, new BeanPropertyRowMapper(bean));
	}

	/**
	 * 查询bean
	 * @author mjm
	 * @history 2011-3-9
	 * @param sql sql查询语句
	 * @param args 参数Object数组
	 * @param bean bean对应class
	 * @return beanlist
	 */
	public Object queryForBean(String sql, Object[] args, Class bean) {
		return queryForObject(sql, args, new BeanPropertyRowMapper(bean));
	}

	/**
	 * 查询单个bean
	 * @author mjm
	 * @history 2011-5-11
	 * @param sql
	 * @param bean
	 * @return
	 */
	public Object queryForBean(String sql, Class bean) {
		return queryForBean(sql, null, bean);
	}



	/**
	* 获得插入语句的主键自增id
	* @author mjm
	* @history 2011-4-8
	* @param sql 插入语句
	* @param args 参数
	* @param pkName 主键名称
	* @return int 主键值
	*/
	public int update(final String sql, final Object[] args, final String pkName) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection)
					throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql, new String[] { pkName });
				for (int i = 0; i < args.length; i++) {
					ps.setObject((i + 1), args[i]);
				}
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}

	/**
	 * 保存单条信息并返回主键
	 * 使用条件：1.bean属性与sql语句中列名一致，
	 * 			2.主键为int自增，
	 * 			3.INSERT语句中必须有列名
	 * @author mjm
	 * @history 2011-4-8
	 * @param sqladd	插入语句
	 * @param sqlupdate 更新语句
	 * @param bean		bean
	 * @param pkName	主键名称
	 * @return	主键值
	 */
	public Integer save(String sqladd, String sqlupdate, Object bean, String pkName)
			throws Exception {
		List infos = new ArrayList();
		infos.add(bean);
		List pks = save(sqladd, sqlupdate, infos, pkName);
		return (Integer) pks.get(0);
	}

	/**
	 * 保存多条信息，返回主键集合
	 * 使用条件，同上
	 * @author mjm
	 * @history 2011-4-8
	 * @param sqladd
	 * @param sqlupdate
	 * @param infos
	 * @param pkName
	 * @return
	 */
	public List save(String sqladd, String sqlupdate, List infos, String pkName) throws Exception {
		List pks = new ArrayList();
		for (Object bean : infos) {
			String pk = BeanUtils.getProperty(bean, pkName);
			if ("".equals(pk) || "0".equals(pk) || pk == null) {
				pks.add(this.update(sqladd, getParsByBean(bean, analysisSql(sqladd)), pkName));
			} else {
				update(sqlupdate, getParsByBean(bean, analysisSql(sqlupdate)));
				pks.add(new Integer(pk));
			}
		}
		return pks;
	}

	/**
	 * 保存不返回主键id
	 * @author mjm
	 * @history 2011-7-5
	 * @param sqladd
	 * @param sqlupdate
	 * @param bean
	 * @param pkName
	 * @throws Exception
	 */
	public void saveNoReturn(String sqladd, String sqlupdate, Object bean, String pkName)
			throws Exception {
		List infos = new ArrayList();
		infos.add(bean);
		saveNoReturn(sqladd, sqlupdate, infos, pkName);
	}

	/**
	 * 保存不返回主键id
	 * @author mjm
	 * @history 2011-7-5
	 * @param sqladd
	 * @param sqlupdate
	 * @param infos
	 * @param pkName
	 * @throws Exception
	 */
	public void saveNoReturn(String sqladd, String sqlupdate, List infos, String pkName)
			throws Exception {
		for (Object bean : infos) {
			String pk = BeanUtils.getProperty(bean, pkName);
			if ("".equals(pk) || "0".equals(pk) || pk == null) {
				this.update(sqladd, getParsByBean(bean, analysisSql(sqladd)));
			} else {
				update(sqlupdate, getParsByBean(bean, analysisSql(sqlupdate)));
			}
		}
	}

	/**
	 * 解析sql语句，并返回需操作的列数组	
	 * @author mjm
	 * @history 2011-4-8
	 * @param sql	sql语句
	 * @param insert是否为添加
	 * @return	操作的列数据
	 */
	public String[] analysisSql(String sql) {
		String[] paramName;
		boolean insert = sql.indexOf("INSERT") > -1 || sql.indexOf("insert") > -1;
		int begin = 0;
		int end = 0;
		if (insert) {
			begin = sql.indexOf("(") + 1;
			end = sql.indexOf(")");
			paramName = sql.substring(begin, end).split(",");
		} else {
			sql = sql.replace("WHERE", ",").replace("where", ",").replaceAll("AND",",");
			sql = sql.replaceAll("=", "");
			sql = sql.replaceAll("[?]", "");
			begin = sql.toUpperCase().indexOf("SET") + 3;
			end = sql.length();
			paramName = sql.substring(begin, end).split(",");
		}
		for (int i = 0; i < paramName.length; i++) {
			paramName[i] = paramName[i].trim();
		}
		return paramName;
	}

	/**
	 * 获得指定属性的bean中属性集合
	 * @author mjm
	 * @history 2011-4-8
	 * @param bean	
	 * @param atts	属性数组
	 * @return	属性对应bean值数组
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 */
	public Object[] getParsByBean(Object bean, String[] atts) throws IllegalAccessException,
			InvocationTargetException, NoSuchMethodException {
		List params = new ArrayList();
		for (int i = 0; i < atts.length; i++) {
			params.add(BeanUtils.getProperty(bean, atts[i]));
		}
		return params.toArray();
	}

	/**
	 * 分页使用查询，简写
	 * @author mjm
	 * @history 2011-7-2
	 * @param sqlcount
	 * @param sql
	 * @param bean
	 * @param cp
	 * @return
	 */
	public Page queryForBeanListByPage(String sqlcount, String sql, Class bean, Page cp) {
		return queryForBeanListByPage(sqlcount, sql, null, bean, cp);
	}

	/**
	 * 分页使用查询
	 * @author mjm
	 * @history 2011-7-2
	 * @param sqlcount 查询行数
	 * @param sql	查询结果
	 * @param args	参数
	 * @param bean 封装的bean
	 * @param cp	分页bean
	 * @return
	 */
	public Page queryForBeanListByPage(String sqlcount, String sql, Object[] args, Class bean,
			Page cp) {
		int totalrows = queryForInt(sqlcount, args);
		cp.setTotalrows(totalrows);
		final int startRow = (cp.getPage() - 1) * cp.getRows();
		final int pageSize = cp.getRows();
		final RowMapper rowMapper = new BeanPropertyRowMapper(bean);
		List pageItems = (List) query(sql, args, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				List result = new ArrayList();
				int currentRow = 0;
				while (rs.next() && currentRow < startRow + pageSize) {
					if (currentRow >= startRow) {
						result.add(rowMapper.mapRow(rs, currentRow));
					}
					currentRow++;
				}
				return result;
			}
		});
		cp.setPageItems(pageItems);
		return cp;
	}
	
	/**
	 * 返回一个beanMap集合<String,Bean>
	 * @author mjm
	 * @history 2011-9-27
	 * @param sql 查询语句
	 * @param args 参数
	 * @param bean bean的类型
	 * @param keyName 作为map中的键，必须在查询语句中存在
	 * @return Map<String,T> T为一个bean类型
	 */
	public <T> Map<String,T> queryforBeanMap(String sql,Object[] args,Class bean,final String keyName){
		final RowMapper rowMapper = new BeanPropertyRowMapper(bean);
		return (Map<String,T>)query(sql, args,new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map<String,T> beanMaptemp = new HashMap(); 
				int currentRow = 0;
				while (rs.next()) {
					T tbean = (T)rowMapper.mapRow(rs, currentRow);
					String key = rs.getString(keyName);
					beanMaptemp.put(key, tbean);
					currentRow++;
				}
				return beanMaptemp;
			}
		});
	}
	
	public Map<String,T> queryforBeanMap(String sql,Class bean,final String keyName){
		return queryforBeanMap(sql, null, bean, keyName);
	}

	/**
	 * 查询前两列的结果影射
	 * 第一列为key 第二列为value
	 * @return Map
	 */
	public Map queryFrontTwoColumnsMap(String sql) {
		return queryFrontTwoColumnsMap(sql, null);
	}
	/**
	 * 查询前两列的结果影射
	 * @return Map
	 */
	public Map queryFrontTwoColumnsMap(String sql, Object[] args) {
		return (Map) query(sql, args, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map m = new HashMap();
				while (rs.next()) {
					m.put(rs.getString(1), rs.getString(2));
				}
				return m;
			}
		});
	}
}
